<?php
class Worker_Model extends CI_Model{
	public function a($worker){
		$data = $this->show($worker);
		$data['book'] = $_SESSION['book'];
		$this->load->view('test/test',$data);
		header("Content-type:application/vnd.ms-excel"); 
		header("Content-Disposition:filename=test.xls"); 
	}

	public function show($worker){
		if($worker){
			$data['info'] = $this->db
				->where('id',$worker)
				->get('worker')
				->row();
			$data['work'] = $this->db
				->query('select book.name,attendance.date,attendance.attendance_days,a.a_d,attendance.attendance_salary,a.a_s
				from attendance
				left join book on book.id = attendance.book_id
				left join (select date,worker_id,attendance_days as a_d,attendance.attendance_salary as a_s from attendance where type = 1)a on a.worker_id = attendance.worker_id and a.date = attendance.date
				where attendance.type = 0 and attendance.worker_id = ?
				UNION
				select book.name,attendance.date,a.attendance_days,attendance.attendance_days as a_d,a.attendance_salary,attendance.attendance_salary as a_s
				from attendance
				left join book on book.id = attendance.book_id
				left join (select date,worker_id,attendance_days,attendance.attendance_salary from attendance where type = 0)a on a.worker_id = attendance.worker_id and a.date = attendance.date
				where attendance.type = 1 and attendance.worker_id = ? ',array($worker,$worker))
				->result();
			$data['payment'] = $this->db
				->where('worker_id',$worker)
				->order_by('date')
				->get('payment')
				->result();
			$data['payment_all'] = $this->db
				->select('sum(amount) as amount')
				->where('worker_id',$worker)
				->get('payment')
				->row();
			$data['attendance_salary'] = $this->db
				->select('sum(attendance_salary) as attendance_salary')
				->where('worker_id',$worker)
				->get('attendance')
				->row();

			return $data;
		}else{
			$data['info'] = $this->db
				->select('worker.*,a.attendance_salary,p.amount')
				->join('worker_book_relation','worker_book_relation.worker_id = worker.id')
				->join('book','book.id = worker_book_relation.book_id','left')
				->join('(select worker_id,sum(amount) as amount from payment group by worker_id)p','p.worker_id = worker.id','left')
				->join('(select worker_id,sum(attendance_salary) as attendance_salary from attendance group by worker_id)a','a.worker_id = worker.id','left')
				->where('book.incharge_id',$_SESSION['id'])
				->get('worker')
				->result();
			return $data;
		}
	}

	public function update($worker){
		$data = array(
			'name'		=>	$worker['name'],
			'tel'		=>	$worker['tel'],
			'salary'	=>	$worker['salary'],
			'remark'	=>	$worker['remark'],
		);

		return $this->db->where('id',$this->uri->segment(3))->update('worker',$data);
		
	}
	// public function new_w($new){
	// 	$data = array(
	// 		'name' => $new['name'],
	// 		'tel' => $new['tel'],
	// 		'wages' => $new['wages'],
	// 		'note' => $new['note'],
	// 	);
	// 	return($this->db->insert('worker',$data));
	// }
}